package yf.platform.biz;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.yfsoft.comm.util.StringUtil;
import org.yfsoft.comm.util.TimeUtil;

import yf.platform.bo.BizResult;
import yf.platform.bo.monitor.IMonitorService;
import yf.platform.bo.monitor.MonitorInfoBean;
import yf.platform.bo.monitor.MonitorServiceImpl;

public class SettingService extends Service {

	public BizResult syslog(Map<String, String> input) throws Throwable {
		return rst
				.setCode(0)
				.setReqAttribute("es",
						this.daoSupport.find("sys_log", null, null, null))
				.setType(BizResult.REDIRECT)
				.setPage("WIDGETS/SETTING/syslog.jsp");
	}

	public BizResult setting(Map<String, String> input) throws Throwable {
		//获取系统的配置信息
		List<Map<String,Object>> settings = this.daoSupport.find("sys_setting", null, null, null);
		IMonitorService service = new MonitorServiceImpl();  
        MonitorInfoBean monitorInfo = service.getMonitorInfoBean();  
        rst.setReqAttribute("monitor", monitorInfo);
        System.out.println("cpu占有率=" + monitorInfo.getCpuRatio());  
          
        System.out.println("可使用内存=" + monitorInfo.getTotalMemory());  
        System.out.println("剩余内存=" + monitorInfo.getFreeMemory());  
        System.out.println("最大可使用内存=" + monitorInfo.getMaxMemory());  
          
        System.out.println("操作系统=" + monitorInfo.getOsName());  
        System.out.println("总的物理内存=" + monitorInfo.getTotalMemorySize() + "kb");  
        System.out.println("剩余的物理内存=" + monitorInfo.getFreeMemory() + "kb");  
        System.out.println("已使用的物理内存=" + monitorInfo.getUsedMemory() + "kb");  
        System.out.println("线程总数=" + monitorInfo.getTotalThread() + "kb"); 
		return rst.setCode(0).setType(BizResult.REDIRECT).setReqAttribute("settings", settings)
				.setPage("WIDGETS/SETTING/setting.jsp");
	}
	
	public BizResult change(Map<String, String> input) throws Throwable {
		//修改系统的配置信息
		for(String k : input.keySet()){
			if(!k.startsWith("SYS_")) continue;
			Map<String,String> e = new HashMap<String,String>();
			e.put("S_VALUE", input.get(k));
			this.daoSupport.update(e, "S_KEY = '"+k+"'", "sys_setting");
		}
		return rst.setCode(0).setType(BizResult.ECHO);
	}

	
	public BizResult port(Map<String, String> input) throws Throwable {
		//加载可导入导出的表数据
		List<Map<String,Object>> udf_tables  =  daoSupport.find("udf_table", null);
		return rst.setCode(0).setReqAttribute("udf_tables", udf_tables)
				.setType(BizResult.REDIRECT)
				.setPage("WIDGETS/SETTING/port.jsp");
	}

	/**
	 * 导入数据到指定的数据表中
	 * @param input
	 * @return
	 * @throws Throwable
	 */
	public BizResult importdata(Map<String, String> input) throws Throwable {
		read(input.get("guid")+"."+input.get("file_type"),input.get("table"));
		return rst.setCode(0).setType(BizResult.REDIRECT)
				.setPage("WIDGETS/SETTING/port.jsp");
	}
	
	/**
	 * 导出数据
	 * @param input
	 * @return
	 * @throws Throwable
	 */
	public BizResult exportdata(Map<String, String> input) throws Throwable {
		String guid = write(input.get("table"));
		return rst.setCode(0).setType(BizResult.ECHO).setData(guid);
	}

	String upload_folder;
	
	String getUploadFolder(){
		if(StringUtil.isNullOrEmpty(upload_folder)){
			Map<String,Object> settings = daoSupport.findOne("sys_setting", "S_KEY,S_VALUE", "S_KEY = 'SYS_UPLOAD'");
			upload_folder = settings.get("S_VALUE").toString();//"D:\\OA-V2\\UPLOAD\\";
		}
		return upload_folder;
	}
	
	/**
	 * 读取excel并导入到指定的数据表中
	 * @param filepath 数据文件的路径
	 * @param table_name 数据表的名称
	 */
	public void read(String filepath,String table_name) {
		try {
			
			Workbook book = Workbook.getWorkbook(new File(getUploadFolder()+filepath));
			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			List<Map<String,String>> es = new ArrayList<Map<String,String>>();
			//第3行为表头
			Cell[] cells = sheet.getRow(2);
			int length = cells.length;
			String[] columns = new String[length];
        	for(int i=0;i<length;i++){
        		columns[i] = cells[i].getContents();
        	}
        	int rows = sheet.getRows();
        	for(int r = 3;r<rows;r++){
        		cells = sheet.getRow(r);
        		Map<String,String> e = new HashMap<String,String>();
        		length = cells.length;
        		for(int i = 0 ; i<length;i++){
        			Cell c = cells[i];
        			e.put(columns[i], c.getContents());
        		}
        		es.add(e);
        	}
            
            book.close(); 
            daoSupport.insert(table_name, es.toArray(new Map[]{}));
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * 导出数据表中的数据并保存到指定的文件中
	 * @param filepath  数据文件的路径
	 * @param table_name 数据表的名称
	 * @throws java.io.IOException
	 * @throws WriteException 
	 */
	public String write(String table_name) throws IOException, WriteException{
		Map<String,String> params = new HashMap<String,String>();
		String guid = null;
		//以数据表名和当前时间作为文件名
		String name = "exp_"+table_name+"@"+TimeUtil.getNow("yyyyMMddHHmm");
		String nowdate = TimeUtil.getNow();
		String title = name;
		params.put("createtime",nowdate);
		params.put("title", name+".xls");
		String ext = "xls";
		params.put("ext", ext);
		guid = StringUtil.getMd5((nowdate + name));
		params.put("guid", guid);
		name = guid+"."+ext;
		params.put("filename", name);
		// 文件的保存路径
		String path = getUploadFolder()+ name ;
		File excelFile = new File(path); 
		if (excelFile.exists())  
			excelFile.delete();
		// 打开文件
		WritableWorkbook book = Workbook.createWorkbook(excelFile);
		// 生成名为title的工作表，参数0表示这是第一页
		WritableSheet sheet = book.createSheet(title, 0);
		
		int r=2,c=0;
		Label label ;
		List<Map<String,Object>> columns = this.daoSupport.desc(table_name);
		String[] colNames = new String[columns.size()];
		for(Map<String,Object> row : columns){
			String colName = row.get("Field").toString();
			colNames[c] = colName;
			label = new Label(c, 0, row.get("Comment").toString());
			sheet.addCell(label);  
			label = new Label(c++, 1, colName);
			sheet.addCell(label);  
		}
		List<Map<String,Object>> entrys = this.daoSupport.find(table_name, null, null, null);
		for(Map<String,Object> e : entrys){
			c = 0;
			String content;
			for(String colName:colNames){
				Object obj =  e.get(colName);
				content = (null==obj?"":obj.toString());
				label = new Label(c++, r, content);
				sheet.addCell(label);  
			}
			r++;
		}
		
		
		book.write();  
		book.close();
		this.daoSupport.insert("usr_upload",params);
		params.clear();
		params.put("d_name", title);
		params.put("d_create_date", nowdate);
		params.put("guid", guid);
		params.put("file_type","xls");
		params.put("d_file",title+".xls");
		this.daoSupport.insert("office_doc",params);
		return guid;
		
	}

}
